#!/usr/bin/env python
# coding:utf-8


import logging
from datatabletransfer import DataTableTransfer
from utils.mysqlutils import MysqlUtils
from fieldsmap import *


class FundSRStatus(DataTableTransfer):
    """
    基金申赎状态从表(TQ_FD_SUBREDSTATUSSUB) --> 基金申赎状态(fund_sub_redem_status)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,SUBREDBEGDATE as status_begin_date,STATUS as sub_redem_status
    from TQ_FD_SUBREDSTATUSSUB ;'''
    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(status_begin_date,'-','') as status_begin_date,
    sub_redem_status from fund_sub_redem_status f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' 
     and b.sec_type=1004003 and b.sec_small_type in(1004003002,1004003003) ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID,STATUS,SUBREDBEGDATE,SUBREDENDDATE
    # from TQ_FD_SUBREDSTATUSSUB limit %s, 1000;''')

    TABLE = 'fund_sub_redem_status'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'status_begin_date':False, 'sub_redem_status':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'status_begin_date': None,
        'status_end_date': None,
    }

    MAP_FIELDS = FundSRStatusMap
    MAP_VALUES = FundSRStatusValueMap


class FundPrize(DataTableTransfer):
    """
    基金获奖情况表(TQ_FD_PRIZESTATE) --> 基金获奖情况(fund_prize)
    """
    QUERY_SOURCE = '''select  PRIZEYEAR as prize_year,case when OBJPRIZE=1 then '基金公司' when OBJPRIZE=2 
    then '基金' else OBJPRIZE end  as prize_object_type,OBJCODE as prize_object_code,PRIZECODE as prize_code
    from TQ_FD_PRIZESTATE  where SPONSORCODE in('80092659','80092661','80070019','80092780');'''

    QUERY_TARGET = '''select prize_year,prize_object_type,prize_object_code,prize_code
     from fund_prize where  status<>'9' ;'''

    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select DECLAREDATE,UPDATEDATE,PRIZEYEAR,case when OBJPRIZE=1 then '基金公司'
    # when OBJPRIZE=2 then '基金' else OBJPRIZE end OBJPRIZE,OBJCODE,OBJNAME,
    # concat('FD',SPONSORCODE) as SPONSORCODE,SPONSORNAME, PRIZECODE,PRIZENAME from TQ_FD_PRIZESTATE
    # where SPONSORCODE in('80092659','80092661','80070019','80092780') limit %s, 1000;''')

    TABLE = 'fund_prize'  # 待插入的表名
    SOURCE_ID = {'prize_year':False, 'prize_object_type':False, 'prize_object_code':False, 'prize_code':True}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'update_date': None,
    }
    IDX = ('prize_code', 'prize_object_type')

    MAP_FIELDS = FundPrizeMap
    MAP_VALUES = FundPrizeValueMap

    @classmethod
    def write_mysql(cls, table, item, column_value, upset):
        """
        更新数据写入MySQL
        :param table:
        :param item:
        :param column_value:
        :param upset:
        :return:
        """

        # 修正prize_object_code的值
        query_code = '''select com_uni_code from org_basic_info where source_id=%s and status!='9' limit 1;'''
        if item['prize_object_type'] == u'基金公司':
            source_id = 'FD{0}'.format(item['prize_object_code'])
            item['prize_object_code'] = cls.exec_sql(conf='data_center', sql=query_code, params=(source_id, ))

        conn = MysqlUtils.connect(cls.data_center)
        if upset and column_value:
            cursor = MysqlUtils.columns_update(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()),
                                               select_column=cls.COLUMN_ID, select_value=column_value)
        else:
            cursor = MysqlUtils.columns_insert(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()))
        if cursor:
            cursor.close()
        conn.close()


class FundCloseOpen(DataTableTransfer):
    """
    基金封转开信息表(TQ_FD_CLOSETOOPEN) --> 基金封转开信息(fund_close_to_open)
    """
    QUERY_SOURCE = '''select  SECURITYID as cfund_uni_code,OFSECURITYID as ofund_uni_code
       from TQ_FD_CLOSETOOPEN a inner join TQ_OA_STCODE b on a.CFSECODE=b.SECODE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as cfund_uni_code,replace(c.source_id,'FD','') as ofund_uni_code
    from fund_close_to_open f join sec_basic_info b on f.cfund_uni_code = b.sec_uni_code join sec_basic_info  c on f.ofund_uni_code = c.sec_uni_code
     where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', b.SECURITYID) as SECURITYID,a.CFSNAME,
    # concat('FD', a.OFSECURITYID) as OFSECURITYID,a.OFSNAME,a.DELISTBEGDATE,a.DECLAREDATE,a.MEETDATE,a.RESULT,
    # a.RESUMEDATE,a.CFENDLISTDATE,a.SUBBEGINDATE,a.SUBFREEZEDATE,a.FDCVTDATE,a.OPENSUBDATE,a.OPENREDEDATE,a.CFSHARE,
    # a.CFSHAREAFSPLIT,a.SUBSHARE,a.SUBANDCVTSHARE
    # from TQ_FD_CLOSETOOPEN a inner join TQ_OA_STCODE b on a.CFSECODE=b.SECODE limit %s, 1000;''')

    TABLE = 'fund_close_to_open'  # 待插入的表名
    SOURCE_ID = {'cfund_uni_code':False, 'ofund_uni_code':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'suspend_beg_date': None,
        'announcement_date': None,
        'meeting_date': None,
        'resumption_date': None,
        'cfund_end_list_date': None,
        'sub_begin_date': None,
        'sub_end_date': None,
        'fund_conversion_date': None,
        'open_sub_date': None,
        'open_redem_date': None,
    }

    MAP_FIELDS = FundCloseOpenMap
    MAP_VALUES = FundCloseOpenValueMap


class FundInherentInvest(DataTableTransfer):
    """
    基金固有资产投资(TQ_FD_INHINVESTMENT) --> 基金固有资金投资(fund_inherent_investment)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,COMPCODE as mana_comp_uni_code,BEGINDATE as begin_date,INVESTTYPE as invest_type
    from TQ_FD_INHINVESTMENT;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(o.source_id,'FD','') as mana_comp_uni_code,
    replace(begin_date,'-','') as begin_date ,invest_type from fund_inherent_investment f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code 
    join org_basic_info  o on f.mana_comp_uni_code = o.com_uni_code  where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,DECLAREDATE,UPDATETDATE,
    # concat('FD', COMPCODE) as COMPCODE,COMPNAME,BEGINDATE,ENDDATE,INVESTTYPE,REDAMT,APPRATE,APPUNIT,INVESTPERIOD,
    # MEMO from TQ_FD_INHINVESTMENT limit %s, 1000;''')

    TABLE = 'fund_inherent_investment'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'mana_comp_uni_code':False, 'begin_date':False, 'invest_type':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'update_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundInherentInvestMap
    MAP_VALUES = FundInherentInvestValueMap


class FundPPQualification(DataTableTransfer):
    """
    基金从业人员资格统计(TQ_FD_FUNDPQUAL)--> 基金从业人员资格(fund_practice_person_qualification)
    """
    QUERY_SOURCE = '''select  COMPCODE as org_uni_code,COMPTYPE as org_type,ENDDATE as end_date
       from TQ_FD_FUNDPQUAL;'''

    QUERY_TARGET = '''select replace(o.source_id,'FD','') as org_uni_code,f.org_type,replace(end_date,'-','') as end_date  
    from fund_practice_person_qualification f join org_basic_info  o on f.org_uni_code = o.com_uni_code  where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', COMPCODE) as COMPCODE,COMPNAME,COMPTYPE,ENDDATE,WORKFORCE,FDQUCATION,
    # FDSQUCATION,OTHQUCATION,PEVCQUCATION,PERRISKCONTROL,FUNDINADVISOR,MANAGERNAME,INVESTMANAGER,INVESTDIRECTOR
    # from TQ_FD_FUNDPQUAL limit %s, 1000;''')

    TABLE = 'fund_practice_person_qualification'  # 待插入的表名
    SOURCE_ID = {'org_uni_code':False, 'org_type':True, 'end_date':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }

    MAP_FIELDS = FundPPQualificationMap
    MAP_VALUES = FundPPQualificationValueMap


class FundXRFactor(DataTableTransfer):
    """
    基金除权因子表(TQ_FD_XDRFACTOR) --> 基金除权因子表(fund_xr_factor)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BONUSTYPE as bonus_type,BONUSYEAR as bonus_year,NUM as dividend_serial_number
       from TQ_FD_XDRFACTOR;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,bonus_type,bonus_year,dividend_serial_number
      from fund_xr_factor f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code   where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID, PUBLISHDATE,SYMBOL,CUR,BONUSTYPE,BONUSYEAR,NUM,
    # AFXDRNAVDATE,UNITPTAXDEV,SHARERT from TQ_FD_XDRFACTOR limit %s, 1000;''')

    TABLE = 'fund_xr_factor'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'bonus_type':True, 'bonus_year':False, 'dividend_serial_number':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'nav_xdr_date': None
    }

    MAP_FIELDS = FundXRFactorMap
    MAP_VALUES = FundXRFactorValueMap


class FundSDRate(DataTableTransfer):
    """
    基金标准券折算率(TQ_FD_STANDDISRATE) --> 基金标准券折算率(fund_std_dis_rate)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,DECLAREDATE as announcement_date from TQ_FD_STANDDISRATE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(announcement_date,'-','') as announcement_date
          from fund_std_dis_rate f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code   where sec_type=1004003 and
          sec_small_type in(1004003002,1004003003) and f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,CONVERSION
    # from TQ_FD_STANDDISRATE limit %s, 1000;''')

    TABLE = 'fund_std_dis_rate'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'announcement_date':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None
    }

    MAP_FIELDS = FundSDRateMap
    MAP_VALUES = FundSDRateValueMap


if __name__ == '__main__':

    log_path = '../logs/export_fund_info3_service.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FundSRStatus.work()
    # FundPrize.work()
    # FundCloseOpen.work()
    # FundInherentInvest.work()
    # FundPPQualification.work()
    # FundXRFactor.work()
    FundSDRate.work()
